var express = require('express');
var router = express.Router();
var query = require('../db');
var dayjs = require('dayjs');

router.post('/updataquestion', async function (req, res, next) {
  try {
    // const sql = 'SELECT * FROM `paper` WHERE `p_name`=? AND `p_author`=? ';
    // let rows = await query(sql, [req.body.paperName, req.body.paperAuthor]);
    // console.log('更新试卷-尝试执行：', sql);
    // console.log(rows.length);
    let tableData = req.body.tableData;
    for (let i = 0; i < tableData.length; i++) {
      if (!tableData[i]['p_id']) {
        // console.log('第', i, '行，是新增的');
        let sql =
        'INSERT INTO paper(p_name, p_author, XH, FS, TG, PT, TMLX, ZQXX, XXA, XXB, XXC, XXD, XXE, XXF)VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )';
        const rows = await query(sql, [
          req.body.paperName,
          req.body.paperAuthor,
          tableData[i]['XH'],
          tableData[i]['FS'],
          tableData[i]['TG'],
          tableData[i]['PT'],
          tableData[i]['TMLX'],
          tableData[i]['ZQXX'],
          tableData[i]['XXA'],
          tableData[i]['XXB'],
          tableData[i]['XXC'],
          tableData[i]['XXD'],
          tableData[i]['XXE'],
          tableData[i]['XXF'],
        ]);
        console.log('第', i, '行是新增的，插入，数据库返回：', rows.affectedRows);
        continue;
      }
      let sql = 'SELECT * FROM `paper` WHERE `p_id`=? ';
      let rows = await query(sql, [tableData[i]['p_id']]);
      rows[0].row_index = tableData[i]['row_index'];
      // console.log(JSON.stringify(rows[0]),'比较');
      // console.log(JSON.stringify(tableData[i]),'比较');
      if (JSON.stringify(rows[0]) === JSON.stringify(tableData[i])) {
        console.log('第', i, '行，未修改，不更新数据库内容');
        continue;
      }
      sql =
        'UPDATE `paper` SET `XH`= ?,`FS`= ?,`TG`= ?,`PT`= ?,`TMLX`= ?,`ZQXX`= ?,`XXA`= ?,`XXB`= ?,`XXC`= ?,`XXD`= ?,`XXE`= ?,`XXF`= ? WHERE `p_id`=?';
      rows = await query(sql, [
        tableData[i]['XH'],
        tableData[i]['FS'],
        tableData[i]['TG'],
        tableData[i]['PT'],
        tableData[i]['TMLX'],
        tableData[i]['ZQXX'],
        tableData[i]['XXA'],
        tableData[i]['XXB'],
        tableData[i]['XXC'],
        tableData[i]['XXD'],
        tableData[i]['XXE'],
        tableData[i]['XXF'],
        tableData[i]['p_id'],
      ]);
      console.log('第', i, '行更新，数据库返回：', rows.message);
    }

    return res.json({
      code: 20000,
      data: { message: '试卷更新成功！', type: 'success' },
    });
    // const sql = 'SELECT * FROM `paper` WHERE `p_name`=? AND `p_author`=? ';
    // let rows = await query(sql, [req.body.paperName, req.body.paperAuthor]);
    // console.log('尝试执行：', sql);
    // console.log(rows.length);
    // return res.json({
    //   code: 20000,
    //   data: rows,
    // });
  } catch (err) {
    return res.json({ message: err.message });
  }
});

router.post('/getquestion', async function (req, res, next) {
  try {
    const sql = 'SELECT * FROM `paper` WHERE `p_name`=? AND `p_author`=? ORDER BY CAST(`XH` AS UNSIGNED INTEGER)';
    let rows = await query(sql, [req.body.paperName, req.body.paperAuthor]);
    console.log('尝试执行：', sql);
    console.log(rows.length);
    return res.json({
      code: 20000,
      data: rows,
    });
  } catch (err) {
    return res.json({ message: err.message });
  }
});

router.post('/deletepaper', async function (req, res, next) {
  try {
    if (req.body.operator !== req.body.paperAuthor) return res.json({ message: '不能删除其他人创建的试卷' });
    const sql = 'DELETE FROM `paper` WHERE `p_name`=? AND `p_author`=?';
    let rows = await query(sql, [req.body.paperName, req.body.operator]);
    console.log('尝试执行：', sql);
    console.log(rows);
    return res.json({
      code: 20000,
      data: { message: '删除成功！', type: 'success' },
    });
  } catch (err) {
    return res.json({ message: err.message });
  }
});

router.post('/getpaper', async function (req, res, next) {
  const sql =
    'SELECT p_name AS paper_name, p_author AS paper_author, MAX(create_time) AS create_time FROM paper GROUP BY `p_name`,`p_author`';
  let rows = await query(sql);
  for (let i = 0; i < rows.length; i++) {
    rows[i].create_time = dayjs(rows[i].create_time).format('YYYY年MM月DD日 HH:mm:ss');
  }
  return res.json({
    code: 20000,
    data: rows,
  });
});

router.post('/upload', async function (req, res, next) {
  const tableData = req.body.tableData;
  const paperTitle = req.body.paperTitle;
  const paperAuthor = req.body.paperAuthor;
  let sql = 'select * from paper where p_name= ? and p_author= ?';
  let rows = await query(sql, [paperTitle, paperAuthor]);
  if (rows.length !== 0) return res.json({ message: '已有名为‘' + paperTitle + '’的考卷，请勿重复提交！' });
  // 校验上传的数组是否为正确数组
  let tableHeader = ['序号', '分数', '题干', '配图', '题目类型', '正确选项', '选项A', '选项B', '选项C', '选项D', '选项E', '选项F'];
  if (JSON.stringify(req.body.tableHeader) !== JSON.stringify(tableHeader)) return res.json({ message: '你上传的试卷格式有误！已驳回' });
  if (tableData.length === 0) return res.json({ message: '你上传的试卷内容为空！已驳回 ' });

  for (let i = 0; i < tableData.length; i++) {
    const sql =
      'INSERT INTO paper(p_name, p_author, XH, FS, TG, PT, TMLX, ZQXX, XXA, XXB, XXC, XXD, XXE, XXF)VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )';
    const rows = await query(sql, [
      paperTitle,
      paperAuthor,
      tableData[i]['序号'],
      tableData[i]['分数'],
      tableData[i]['题干'],
      tableData[i]['配图'],
      tableData[i]['题目类型'],
      tableData[i]['正确选项'],
      tableData[i]['选项A'],
      tableData[i]['选项B'],
      tableData[i]['选项C'],
      tableData[i]['选项D'],
      tableData[i]['选项E'],
      tableData[i]['选项F'],
    ]);
    console.log('插入paper表：', 'XH / p_name: ', tableData[i]['序号'], '/', paperTitle,"==",rows.message);
  }
  return res.json({
    code: 20000,
    data: '上传成功！',
  });
});

module.exports = router;
